import logging
from datetime import datetime
from decimal import Decimal
from typing import Union, Optional, List

from fastapi import APIRouter, Depends, Query, Path
from fastapi.security import oauth2
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

ord_head_router = APIRouter(prefix="/ordHead", tags=["耗材订单管理"], dependencies=[Depends(oauth2_scheme)])


class OrdSearch(PaginationBase):
    forduser: str = Field(None, description="采购人员")
    fsupno: str = Field(None, description="供应商编号")
    fsiteNo: str = Field(None, description="客户编号")
    ftakedateStart: str = Field(None, description="开始时间")
    ftakedateEnd: str = Field(None, description="结束时间")
    fbilltype: str = Field(None, description="报废类型(1:仓库报废/2:储物柜报废)")
    fstockno: str = Field(None, description="仓库编号")
    fbillno: str = Field(None, description="单号")
    fbillstate: str = Field(None, description="单据状态(1:未报废/2:已报废/8:已作废)")


@ord_head_router.get("/list", summary="查询耗材订单")
def list_ord_head(ord: OrdSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.m_tc_ord_head where 1=1"
    if ord.fsiteNo is not None:
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = ord.fsiteNo
    if ord.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = ord.fbillno
    if ord.forduser is not None:
        sql = sql + " and forduser = :forduser "
        param["forduser"] = ord.forduser
    if ord.fsupno is not None:
        sql = sql + " and fsupno = :fsupno "
        param["fsupno"] = ord.fsupno
    if ord.fstockno is not None:
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = ord.fstockno
    if ord.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = ord.fbillstate
    if (ord.ftakedateStart is not None) and (ord.ftakedateEnd is not None):
        sql = sql + " and to_char(forddate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = ord.ftakedateStart
        param["ftakedateEnd"] = ord.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,forduser,forddate,fplanrecdate,fstockno,fsupno,fitemcount,fpiececount,"
    sql = sql + " ftotalamt,fsrcbillno,fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate,fbilltype"
    sql = sql + " from lims.m_tc_ord_head where 1=1"
    if ord.fsiteNo is not None:
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = ord.fsiteNo
    if ord.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = ord.fbillno
    if ord.forduser is not None:
        sql = sql + " and forduser = :forduser "
        param["forduser"] = ord.forduser
    if ord.fsupno is not None:
        sql = sql + " and fsupno = :fsupno "
        param["fsupno"] = ord.fsupno
    if ord.fstockno is not None:
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = ord.fstockno
    if ord.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = ord.fbillstate
    if (ord.ftakedateStart is not None) and (ord.ftakedateEnd is not None):
        sql = sql + " and to_char(forddate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = ord.ftakedateStart
        param["ftakedateEnd"] = ord.ftakedateEnd
    if ord.order == "desc":
        sql = sql + " order by  forddate,fbillno desc "
    else:
        sql = sql + " order by  forddate,fbillno "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = ord.limit
    param["offset"] = ord.offset
    data = db.execute(text(sql), param).fetchall()

    # 明细表
    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fmaterialno,b.fbuyprice,b.fqty,b.ftotamt,b.fmemo"
    sql_3 = sql_3 + " from lims.m_tc_ord_head a,lims.m_tc_ord_detail b where a.fbillno=b.fbillno "
    if ord.fsiteNo != "":
        sql_3 = sql_3 + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = ord.fsiteNo
    if ord.fbillno != "":
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = ord.fbillno
    if ord.forduser != "":
        sql_3 = sql_3 + " and a.forduser = :forduser "
        param["forduser"] = ord.forduser
    if ord.fsupno != "":
        sql_3 = sql_3 + " and a.fsupno = :fsupno "
        param["fsupno"] = ord.fsupno
    if ord.fstockno != "":
        sql_3 = sql_3 + " and a.fstockno = :fstockno "
        param["fstockno"] = ord.fstockno
    if ord.fbillstate != "":
        sql_3 = sql_3 + " and a.fbillstate = :fbillstate "
        param["fbillstate"] = ord.fbillstate
    if (ord.ftakedateStart != "") and (ord.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(a.forddate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = ord.ftakedateStart
        param["ftakedateEnd"] = ord.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        forddate = ""
        try:
            forddate = data[key][3].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (forddate == ""):
            forddate = None

        fplanrecdate = ""
        try:
            fplanrecdate = data[key][4].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fplanrecdate == ""):
            fplanrecdate = None

        fupddate = ""
        try:
            fupddate = data[key][12].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fupddate == ""):
            fupddate = None

        fcheckdate = ""
        try:
            fcheckdate = data[key][14].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fcheckdate == ""):
            fcheckdate = None

        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['forduser'] = data[key][2]
        d1['forddate'] = forddate
        d1['fplanrecdate'] = fplanrecdate
        d1['fstockno'] = data[key][5]
        d1['fsupno'] = data[key][6]
        d1['fitemcount'] = data[key][7]
        d1['fpiececount'] = data[key][8]
        d1['ftotalamt'] = data[key][9]
        d1['fsrcbillno'] = data[key][10]
        d1['fexplanation'] = data[key][11]
        d1['fupddate'] = fupddate
        d1['foperator'] = data[key][13]
        d1['fcheckdate'] = fcheckdate
        d1['fcheckuser'] = data[key][15]
        d1['fbillstate'] = data[key][16]
        d1['fbilltype'] = data[key][17]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fmaterialno'] = data_detail[j1][2]
                d2['fbuyprice'] = float(data_detail[j1][3])
                d2['fqty'] = float(data_detail[j1][4])
                d2['ftotamt'] = float(data_detail[j1][5])
                d2['fmemo'] = data_detail[j1][6]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": ord.limit, "page": ord.pageIndex,
                 "pages": (count + ord.limit - 1) // ord.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class OrderDetailCreate(BaseModel):
    """耗材订单明细项的数据模型"""
    fline: int = Field(description="行号")
    fmaterialno: str = Field(description="耗材编号")
    fbuyprice: Union[str, float, Decimal] = Field(default=Decimal("0"), description="购买价格")
    fqty: Union[str, float, Decimal] = Field(default=Decimal("0"), description="数量")
    ftotamt: Union[str, float, Decimal] = Field(default=Decimal("0"), description="总金额")
    fmemo: str = Field(default="", description="备注")
    fmaterialname: Optional[str] = Field(default="", description="耗材名称")


class OrderHeadCreate(BaseModel):
    """耗材订单头信息的数据模型"""
    fbillno: str = Field(description="单据编号")
    fbillstate: int = Field(description="单据状态")
    forddate: Union[datetime, str] = Field(description="订单日期")
    forduser: str = Field(description="下单用户")
    fplanrecdate: Union[datetime, str] = Field(description="计划收货日期")
    fsiteNo: str = Field(description="站点编号")
    fstockno: str = Field(description="仓库编号")
    fsupno: str = Field(description="供应商编号")
    fexplanation: str = Field(default="", description="说明")
    details: List[OrderDetailCreate] = Field(description="订单明细列表")


@ord_head_router.post("/create", summary="新增耗材订单")
def create_ord_head(ord: OrderHeadCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = ord.fbillno
        # 返回内容
        d1 = {}
        detail_list = []
        user_id = EncryptionMiddleware.decrypt_token(token)
        with db.begin():
            # 新增详情
            details = ord.details
            for data in details:
                sql = "insert into lims.m_tc_ord_detail(fbillno,fline,fmaterialno,fbuyprice,fqty,ftotamt,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fmaterialno , :fbuyprice , :fqty , :ftotamt ,'')"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fmaterialno": data.fmaterialno,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.ftotamt,
                    "ftotamt": data.ftotamt,
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + data.fqty
                ftotalamt = ftotalamt + data.ftotamt

                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data['fline']
                d2['fmaterialno'] = data['fmaterialno']
                d2['fmaterialname'] = data['fmaterialname']
                d2['fbuyprice'] = float(data['fbuyprice'])
                d2['fqty'] = float(data['fqty'])
                d2['ftotamt'] = float(data['ftotamt'])
                d2['fmemo'] = ""
                detail_list.append(d2)
            sql = "insert into lims.m_tc_ord_head(fbillno,fsite_no,forduser,forddate,fplanrecdate ,fstockno,fsupno,fitemcount,fpiececount,ftotalamt ,fexplanation,fupddate,foperator,fbillstate,fbilltype)"
            sql = sql + " values( :fbillno , :fsiteNo , :forduser , :forddate , :fplanrecdate , :fstockno , :fsupno , :fitemcount , :fpiececount , :ftotalamt , :fexplanation ,now(), :forduser ,1,1)"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": ord.fsiteNo,
                "forduser": ord.forduser,
                "forddate": ord.forddate,
                "fplanrecdate": ord.fplanrecdate,
                "fstockno": ord.fstockno,
                "fsupno": ord.fsupno,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "fexplanation": ord.fexplanation,
                "foperator": user_id,
            })

            # 返回
            d1['fbillno'] = fbillno
            d1['fsiteno'] = ord.fsiteNo
            d1['forduser'] = ord.forduser
            d1['forddate'] = ord.forddate
            d1['fplanrecdate'] = ord.fplanrecdate
            d1['fstockno'] = ord.fstockno
            d1['fsupno'] = ord.fsupno
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftotalamt'] = float(ftotalamt)
            d1['fsrcbillno'] = ""
            d1['fexplanation'] = ""
            d1['fupddate'] = ord.fplanrecdate
            d1['foperator'] = ord.forduser
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbillstate'] = 1
            d1['fbilltype'] = 1
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@ord_head_router.post("/update", summary="修改耗材订单")
def update_ord_head(ord: OrderHeadCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        sqllist = []
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = ord.fbillno
        user_id = EncryptionMiddleware.decrypt_token(token)
        with db.begin():
            sql = "delete from lims.m_tc_ord_detail where fbillno= :fbillno "
            db.execute(text(sql), {"fbillno": fbillno})

            # 新增明细
            details = ord.details
            for data in details:
                sql = "insert into lims.m_tc_ord_detail(fbillno,fline,fmaterialno,fbuyprice,fqty,ftotamt,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fmaterialno , :fbuyprice , :fqty , :ftotamt ,'')"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fmaterialno": data.fmaterialno,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.fqty,
                    "ftotamt": data.ftotamt,
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + data.fqty
                ftotalamt = ftotalamt + data.ftotamt

            sql = "update lims.m_tc_ord_head "
            sql = sql + " set fstockno= :fstockno ,fsupno= :fsupno ,fexplanation= :fexplanation "
            sql = sql + ",fitemcount= :fitemcount ,fpiececount= :fpiececount ,ftotalamt= :ftotalamt "
            sql = sql + ",fupddate=now(),foperator= :userid "
            sql = sql + " where fbillno= :fbillno "

            db.execute(text(sql), {
                "fstockno": ord.fsiteNo,
                "fsupno": ord.fsupno,
                "fexplanation": ord.fexplanation,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "userid": user_id,
                "fbillno": fbillno
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@ord_head_router.post("/delete/{fbillno}", summary="删除耗材订单")
def delete_order_head(fbillno: str = Path(description="订单号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select count(1) from lims.m_tc_ord_head where fbillno= :fbillno and fbillstate>1"
            data = db.execute(text(sql), {"fbillno": fbillno}).scalar()
            if data > 0:
                return BaseResponse.error(message="该订单不能删除")
            sql = "delete from lims.m_tc_ord_detail where fbillno= :fbillno "
            db.execute(text(sql), {"fbillno": fbillno})
            sql = "delete from lims.m_tc_ord_head where fbillno= :fbillno "
            db.execute(text(sql), {"fbillno": fbillno})
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
